{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1219343f-add9-41c0-8ede-e626a99baa3f",
   "metadata": {},
   "source": [
    "![Banner](images/banner.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8683213-8402-4b0b-9775-8eab75292a7e",
   "metadata": {},
   "source": [
    "# Working with the VECTOR data type\n",
    "\n",
    "This section requires Oracle AI Database 26ai\n",
    "\n",
    "Documentation reference link: [Using VECTOR Data](https://python-oracledb.readthedocs.io/en/latest/user_guide/vector_data_type.html).\n",
    "\n",
    "<hr>\n",
    "\n",
    "Setup for this notebook:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b2e8ee24-171a-412b-be64-b0755979ee02",
   "metadata": {},
   "outputs": [],
   "source": [
    "import array\n",
    "import os\n",
    "import oracledb\n",
    "\n",
    "un = os.environ.get(\"PYO_SAMPLES_MAIN_USER\", \"pythondemo\")\n",
    "pw = os.environ.get(\"PYO_SAMPLES_MAIN_PASSWORD\", \"welcome\")\n",
    "cs = os.environ.get(\"PYO_SAMPLES_CONNECT_STRING\", \"localhost/orclpdb\")\n",
    "\n",
    "connection = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "\n",
    "if tuple(int(s) for s in connection.version.split(\".\"))[:2] < (23, 7):\n",
    "    print(\"!! This notebook requires Oracle Database 23.7 or later !!\")\n",
    "\n",
    "cursor = connection.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca3d046c-86d9-4743-a238-826ed579659d",
   "metadata": {},
   "source": [
    "## The VECTOR data type\n",
    "\n",
    "Oracle AI Database 26ai introduces a VECTOR data type and Unified Hybrid Vector Search. You can blend vectors with relational, text, JSON, graph, and spatial predicates in a single query to retrieve documents, images, audio, video, and table rows together. \n",
    "\n",
    "Each VECTOR is represented as a number of vectors and a data format. For example this table holds rows where the first column contains three vectors of 32-bit numbers, the second column holds three vectors of 64-bit numbers, and the last column holds three vectors of 8-bit integers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a38f25b-b72c-4dc2-98ea-de5f1797f988",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"drop table if exists vtab\")\n",
    "\n",
    "cursor.execute(\"\"\"create table vtab (\n",
    "                               v32  vector(3, float32),\n",
    "                               v64  vector(3, float64),\n",
    "                               v8   vector(3, int8))\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5f57b50-f8eb-40a6-a247-8179f12bdbc9",
   "metadata": {},
   "source": [
    "The Python `array.array()` class is used to represent vectors:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acf55243-b0c3-464a-a5e2-bc952484fc55",
   "metadata": {},
   "outputs": [],
   "source": [
    "vector_data_32 = array.array(\"f\", [2.625, 2.5, 2.0])\n",
    "vector_data_64 = array.array(\"d\", [22.25, 22.75, 22.5])\n",
    "vector_data_8 = array.array(\"b\", [4, 5, 6])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "697d5208-af6a-49df-b9e2-c1df4eb9ffbf",
   "metadata": {},
   "source": [
    "Insert the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f68aa51-b1de-412c-a8c4-1dd891b52b7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\n",
    "    \"insert into vtab (v32, v64, v8) values (:1, :2, :3)\", \n",
    "    [vector_data_32, vector_data_64, vector_data_8]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3886080a-f9cd-4c86-8303-2a2f36a43974",
   "metadata": {},
   "source": [
    "Verify it was inserted correctly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "189250d7-ae5a-4297-a26b-4546701f4c14",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in cursor.execute(\"select v32, v64, v8 from vtab\"):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "859c184a-c470-4c7a-ac2b-7a41b4e8504e",
   "metadata": {},
   "source": [
    "## Binary Vectors\n",
    "\n",
    "Documentation reference link: [Using BINARY Vectors](https://python-oracledb.readthedocs.io/en/latest/user_guide/vector_data_type.html#using-binary-vectors).\n",
    "\n",
    "The BINARY format for VECTOR is an efficient way to store 0 and 1 values.\n",
    "\n",
    "You must define the number of dimensions as a multiple of 8. Rows in the `vbin` column of this table hold 24 binary values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "079ffd5f-9705-4720-a473-0ccbc12431ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"drop table if exists vtab\")\n",
    "\n",
    "cursor.execute(\"create table vtab (vbin vector(24, binary))\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ca087a2-6da1-4da0-b2a0-15abcd7db795",
   "metadata": {},
   "source": [
    "Binary vectors are represented as 8-bit unsigned integers so the 24 bit values are inserted as three 8-bit unsigned integers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0dc8d51-ea14-4d5f-b287-225b19760624",
   "metadata": {},
   "outputs": [],
   "source": [
    "vector_data_bin = array.array(\"B\", [40, 15, 255])\n",
    "\n",
    "cursor.execute(\"insert into vtab (vbin) values (:1)\", [vector_data_bin])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcaaaba8-f2ac-4738-93ee-fc78c1db3266",
   "metadata": {},
   "source": [
    "Verify it was inserted correctly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c83f1260-4e21-4ac0-96bb-ef6a2239344d",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in cursor.execute(\"select vbin from vtab\"):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74325ada-531e-4584-918a-8b339642fd5c",
   "metadata": {},
   "source": [
    "## Sparse Vectors\n",
    "\n",
    "Documentation reference link: [Using SPARSE Vectors](https://python-oracledb.readthedocs.io/en/latest/user_guide/vector_data_type.html#using-sparse-vectors).\n",
    "\n",
    "Vectors can be declared as SPARSE, which saves space when most values are zero:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c18d6e2-0268-4134-9b4b-558964352d96",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"drop table if exists vtab\")\n",
    "\n",
    "cursor.execute(\"create table vtab (v64sparse vector(30, float64, sparse))\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee5b7de1-ba93-4796-ab9e-563b4088aaf3",
   "metadata": {},
   "source": [
    "Sparse vector data is represented by a maximum length of the vector, and two arrays. The first array contains data indexes, and the second array contains the non-zero data values corresponding to the indexes. In python-oracledb, a SparseVector class encapsulates this information:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b130da07-4f12-4924-a867-e19d791f8690",
   "metadata": {},
   "outputs": [],
   "source": [
    "vector_data_sparse64 = oracledb.SparseVector(30, [3, 10, 12], array.array(\"d\", [2.5, 2.5, 1.0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "225f7fd0-015e-4883-86fc-d4cb50ddb0fa",
   "metadata": {},
   "source": [
    "Insertion is simply a matter of binding the vector:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e896fa56-c138-46ac-950d-6625ac927d7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"insert into vtab (v64sparse) values (:1)\", [vector_data_sparse64])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2b6be65-5dd1-468b-b8f4-5a4570395f6e",
   "metadata": {},
   "source": [
    "Verify it was inserted correctly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d43698d-326c-4432-bb61-0d852f3287cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in cursor.execute(\"select v64sparse from vtab\"):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0549c1a1-6729-43b9-bd44-1a092c92bec5",
   "metadata": {},
   "source": [
    "## VECTOR Query Metadata\n",
    "\n",
    "Query metadata can be used to describe vector columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e03f3dbb-2b3c-403d-aa8b-6af75f46767f",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"select v64sparse from vtab\")\n",
    "desc = cursor.description[0]\n",
    "\n",
    "print(desc.vector_format, desc.vector_dimensions, desc.vector_is_sparse)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.24"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
